<?php
	include_once "dataservice.php";
	include_once "thuvienham.php";
	
	class baiviet_db
	{
		public function Them($MaBaiViet, $MaChuyenMuc, $MaNguoiDung, $TieuDe,
							$NgayDang, $TomTat, $NoiDung, $HinhAnh, 
							$ChuThichHinhAnh, $MaTrangThai, $LuotXem,
							$DuocChinhSua, $PIN, $TuKhoa, $ChoPhepBinhLuan)
		{
			$sql = "INSERT 	INTO `tbl_baiviet`(`MaBaiViet`, `MaChuyenMuc`, `MaNguoiDung`, `TieuDe`, 
											  `NgayDang`, `TomTat`, `NoiDung`, `HinhAnh`, 
											  `ChuThichHinhAnh`, `MaTrangThai`, `LuotXem`, `DuocChinhSua`, `PIN`, `TuKhoa`, `ChoPhepBinhLuan`) 
							VALUES ('$MaBaiViet','$MaChuyenMuc','$MaNguoiDung','$TieuDe',
									'$NgayDang','$TomTat','$NoiDung','$HinhAnh',
									'$ChuThichHinhAnh','$MaTrangThai','$LuotXem', '$DuocChinhSua', '$PIN', '$TuKhoa', '$ChoPhepBinhLuan')";
					
			$service = new dataservice();
			return $service->ExecuteNonQuery($sql);
		}
		
		public function CapNhat($MaBaiViet, $MaChuyenMuc, $MaNguoiDung, $TieuDe, 
								$NgayDang, $TomTat, $NoiDung, $HinhAnh,
								$ChuThichHinhAnh, $MaTrangThai,$LuotXem, 
								$DuocChinhSua, $PIN, $TuKhoa)
		{
			$sql = "UPDATE 	`tbl_baiviet` 
					SET 	`MaChuyenMuc`     = '$MaChuyenMuc',
							`MaNguoiDung`     = '$MaNguoiDung',
							`TieuDe`          = '$TieuDe',
							`NgayDang`        = '$NgayDang',
							`TomTat`          = '$TomTat',
							`NoiDung`         = '$NoiDung',
							`HinhAnh`         = '$HinhAnh',
							`ChuThichHinhAnh` = '$ChuThichHinhAnh',
							`MaTrangThai`     = '$MaTrangThai',
							`LuotXem`         = '$LuotXem',
							`DuocChinhSua`    = '$DuocChinhSua',
							`PIN`             = '$PIN',
							`TuKhoa`          = '$TuKhoa'
					WHERE 	`MaBaiViet`       = '$MaBaiViet'";
			
			$service = new dataservice();
			return $service->ExecuteNonQuery($sql);
		}
		
		public function CapNhatDuocChinhSua($MaBaiViet, $DuocChinhSua)
		{
			$sql = "UPDATE 	`tbl_baiviet` 
					SET 	`DuocChinhSua` = '$DuocChinhSua' 
					WHERE 	`MaBaiViet`='$MaBaiViet'";
			
			$service = new dataservice();
			return $service->ExecuteNonQuery($sql);
		}
		public function CapNhatTrangThai($MaBaiViet, $MaTrangThai)
		{
			$sql = "UPDATE 	`tbl_baiviet` 
					SET 	`MaTrangThai` = '$MaTrangThai' 
					WHERE 	`MaBaiViet`='$MaBaiViet'";
			
			$service = new dataservice();
			return $service->ExecuteNonQuery($sql);
		}
		public function CapNhatPIN($MaBaiViet, $PIN)
		{
			$sql = "UPDATE 	`tbl_baiviet` 
					SET 	`PIN` = '$PIN' 
					WHERE 	`MaBaiViet`='$MaBaiViet'";
			
			$service = new dataservice();
			return $service->ExecuteNonQuery($sql);
		}
		
		public function Xoa($MaBaiViet)
		{
			$sql = "DELETE FROM `tbl_baiviet` WHERE `MaBaiViet` = '$MaBaiViet'";
			
			$service = new dataservice();
			return $service->ExecuteNonQuery($sql);
		}
		
		public function LayDanhSach($MaBaiViet, $MaNguoiDung, $MaTrangThai)
		{
			$sql = "SELECT * FROM `tbl_baiviet` WHERE 1 ";
			
			if($MaBaiViet != 0)
				$sql .= " AND `MaBaiViet` = '$MaBaiViet' ";
			if($MaNguoiDung != 0)
				$sql .= " AND `MaNguoiDung` = '$MaNguoiDung' ";
			if($MaTrangThai != -1)
				$sql .= " AND `MaTrangThai` = '$MaTrangThai' ";
			$sql .= " ORDER BY `NgayDang` DESC";
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		
		public function Lay15BaiVietMoiNhat()
		{
			$sql = "SELECT * FROM  `tbl_baiviet` ORDER BY  `NgayDang` DESC ";
			
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		public function LayBaiVietTheoNgayGiamDan($MaTrangThai)
		{
			$sql = "SELECT * FROM  `tbl_baiviet` ";
			if($MaTrangThai != -1)
				$sql .= " WHERE `MaTrangThai` = '$MaTrangThai' ";
			$sql .= " ORDER BY  `NgayDang` DESC";
			
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		public function LayBaiVietTheoLuotXemGiamDan($MaTrangThai)
		{
			$sql = "SELECT * FROM  `tbl_baiviet`";
			if($MaTrangThai != -1)
				$sql .= " WHERE `MaTrangThai` = '$MaTrangThai' ";
			$sql .= " ORDER BY  `LuotXem` DESC";
			
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		public function LayBaiVietTrongNgay($MaTrangThai)
		{
			date_default_timezone_set('Asia/Ho_Chi_Minh');
			$Ngay = date('y-m-d h:i:s a', time());
			$tmp_Ngay = LayNgay($Ngay);
			$sql = "SELECT * FROM  `tbl_baiviet` WHERE  `NgayDang` LIKE  '$tmp_Ngay%' ";
			if($MaTrangThai != -1)
				$sql .= " AND `MaTrangThai` = '$MaTrangThai' ";
			$sql .= "ORDER BY  `LuotXem` DESC";
			
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		public function LayBaiVietMoiNhatTheoChuDeBaiViet($MaBaiViet, $MaTrangThai) //Tin liên quan
		{
			$sql = "SELECT 	BV . * 
					FROM  	`tbl_baiviet` BV,  `tbl_chuyenmuc` CM,  `tbl_chude` CD
					WHERE 	BV.MaChuyenMuc = CM.MaChuyenMuc
							AND CM.MaChuDe = CD.MaChuDe
							AND BV.MaBaiViet <> $MaBaiViet
							AND CM.MaChuDe = (
												SELECT CM.MaChuDe 
												FROM `tbl_baiviet` BV,  `tbl_chuyenmuc` CM,  `tbl_chude` CD
												WHERE BV.MaChuyenMuc = CM.MaChuyenMuc
													AND BV.MaBaiViet = $MaBaiViet
													AND CM.MaChuDe = CD.MaChuDe
											  ) ";
			if($MaTrangThai != -1)
				$sql .= " AND  BV.MaTrangThai = 1";
			$sql .= " ORDER BY BV.NgayDang DESC";
					
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		public function DanhSachBaiVietTheoNguoiDung($MaNguoiDung)
		{
			$sql = "SELECT * FROM  `tbl_baiviet` 
					WHERE  `MaNguoiDung`='$MaNguoiDung' 
					ORDER BY  `NgayDang` DESC";
					
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		public function DemSoBaiVietTheoMaNguoiDung($MaNguoiDung = "")
		{
			$sql = "SELECT COUNT(*) FROM  `tbl_baiviet` 
					WHERE  `MaNguoiDung`='$MaNguoiDung'";
					
			$service = new dataservice();
			return $service->ExecuteScalar($sql);
		}
		
		public function LayBaiVietTheoChuDe($MaChuDe, $MaTrangThai) //Theo thời gian giảm dần
		{
			$sql = "SELECT BV.*, CD.MaChuDe
					FROM tbl_baiviet BV, tbl_chuyenmuc CM, tbl_chude CD
					WHERE BV.MaChuyenMuc = CM.MaChuyenMuc AND
						  CM.MaChuDe = CD.MaChuDe AND
						  CD.MaChuDe = '$MaChuDe' ";
			if($MaTrangThai != -1)
				$sql .= " AND BV.MaTrangThai = '$MaTrangThai' ";
			$sql .= " ORDER BY `NgayDang` DESC";
					
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		public function LayBaiVietTheoChuyenMuc($MaChuyenMuc, $MaTrangThai) //Theo thời gian giảm dần
		{
			$sql = "SELECT *
					FROM tbl_baiviet 
					WHERE MaChuyenMuc = '$MaChuyenMuc' ";
			if($MaTrangThai != -1)
				$sql .= " AND MaTrangThai = '$MaTrangThai'";
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		public function TangMotLuotXem($MaBaiViet)
		{
			$sql = "UPDATE `tbl_baiviet` SET `LuotXem`=(`LuotXem`+1) WHERE `MaBaiViet`='$MaBaiViet'";
			
			$service = new dataservice();
			return $service->ExecuteNonQuery($sql);
		}
		
		public function LayMaBaiVietLonNhat()
		{
			$sql = "SELECT MAX(`MaBaiViet`) FROM  `tbl_baiviet`";
					
			$service = new dataservice();
			return $service->ExecuteScalar($sql);
		}
		
		public function LayTieuDeTheoMaBaiViet($MaBaiViet)
		{
			$sql = "SELECT `TieuDe` FROM  `tbl_baiviet` WHERE `MaBaiViet`='$MaBaiViet'";
					
			$service = new dataservice();
			return $service->ExecuteScalar($sql);
		}
		
		public function LayBaiVietPhanTrang($MaTrangThai, $MaNguoiDung, $Start, $Limit)
		{
			$sql = "SELECT * FROM  `tbl_baiviet` ";
			if($MaTrangThai != -1)
				$sql .= " WHERE `MaTrangThai` = '$MaTrangThai' ";
			if($MaNguoiDung != 0)
				$sql .= " WHERE `MaNguoiDung` = '$MaNguoiDung' ";
			$sql .= " ORDER BY  `NgayDang` DESC LIMIT $Start, $Limit ";
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		public function LayBaiVietTheoChuDePhanTrang($MaChuDe, $MaTrangThai, $Start, $Limit)
		{
			$sql = "SELECT BV.*, CD.MaChuDe
					FROM tbl_baiviet BV, tbl_chuyenmuc CM, tbl_chude CD
					WHERE BV.MaChuyenMuc = CM.MaChuyenMuc AND
						  CM.MaChuDe = CD.MaChuDe AND
						  CD.MaChuDe = '$MaChuDe' ";
		    if($MaTrangThai != -1)
				$sql .= " AND BV.MaTrangThai = '$MaTrangThai' ";
			$sql .= "ORDER BY  `NgayDang` DESC LIMIT $Start, $Limit";
			
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		public function LayBaiVietTheoChuyenMucPhanTrang($MaChuyenMuc, $MaTrangThai, $Start, $Limit)
		{
			$sql = "SELECT *
					FROM tbl_baiviet 
					WHERE MaChuyenMuc = '$MaChuyenMuc' ";
			if($MaTrangThai != -1)
				$sql .= " AND MaTrangThai = '$MaTrangThai' ";
			$sql .= "ORDER BY  `NgayDang` DESC LIMIT $Start, $Limit";
			
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		public function LayBaiVietTheoTuKhoa($MaTrangThai, $TuKhoa)
		{
			$sql = "SELECT 	*
					FROM 	tbl_baiviet 
					WHERE 	(`TuKhoa` LIKE '%$TuKhoa%' OR
							`TieuDe` LIKE '%$TuKhoa%' OR
							`TomTat` LIKE '%$TuKhoa%') ";
			if($MaTrangThai != -1)
				$sql .= " AND MaTrangThai = '$MaTrangThai' ";
			$sql .= "ORDER BY  `NgayDang` DESC";
			
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		public function LayBaiVietTheoTuKhoaPhanTrang($MaTrangThai, $TuKhoa, $Start, $Limit)
		{
			$sql = "SELECT 	*
					FROM 	tbl_baiviet 
					WHERE 	(`TuKhoa` LIKE '%$TuKhoa%' OR
							`TieuDe` LIKE '%$TuKhoa%' OR
							`TomTat` LIKE '%$TuKhoa%') ";
			if($MaTrangThai != -1)
				$sql .= " AND MaTrangThai = '$MaTrangThai' ";
			$sql .= "ORDER BY  `NgayDang` DESC LIMIT $Start, $Limit";
			
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		public function LayMaChuDeBaiViet($MaBaiViet)
		{
			$sql = "SELECT CM.MaChuDe
					FROM tbl_baiviet BV, tbl_ChuyenMuc CM
					WHERE BV.MaChuyenMuc = CM.MaChuyenMuc
					AND BV.MaBaiViet =  '$MaBaiViet'";
					
			$service = new dataservice();
			return $service->ExecuteScalar($sql);
		}
		
		public function IsTinChinh($MaBaiViet)
		{
			$sql = "SELECT 	COUNT(*) 
					FROM 	`tbl_baiviet` BV, `tbl_tinchinh` TC
					WHERE 	BV.MaBaiViet = '$MaBaiViet' AND
							BV.MaBaiViet = TC.MaBaiViet";
					
			$service = new dataservice();
			return $service->ExecuteScalar($sql);
		}
		
		public function TimKiem($TuKhoa)
		{
			$sql = "SELECT 	* 
					FROM 	`tbl_baiviet` 
					WHERE 	`TuKhoa` LIKE '%$TuKhoa%' OR
							`TieuDe` LIKE '%$TuKhoa%' OR
							`TomTat` LIKE '%$TuKhoa%' 
					ORDER BY `NgayDang` DESC";
					
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		
		public function KiemTraTonTai($MaBaiViet)
		{
			$sql = "SELECT 	COUNT(*) 
					FROM 	`tbl_baiviet` 
					WHERE 	`MaBaiViet` = '$MaBaiViet'";
					
			$service = new dataservice();
			return $service->ExecuteScalar($sql);
		}
		
		public function LayDanhSachTheoLuotThich()
		{
			$sql = "SELECT BV.*, COUNT(BV.MaBaiViet) AS LuotThich
					FROM `tbl_baiviet` BV
					INNER JOIN `tbl_thichbaiviet` TBV
					ON BV.MaBaiViet = TBV.MaBaiViet
					GROUP BY BV.MaBaiViet
					ORDER BY LuotThich DESC";
					
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
		public function LayDanhSachTheoLuotBinhLuan()
		{
			$sql = "SELECT BV.*, COUNT( BV.MaBaiViet ) AS LuotBinhLuan
					FROM tbl_baiviet BV
					INNER JOIN tbl_binhluan BL ON BV.MaBaiViet = BL.MaBaiViet
					GROUP BY BV.MaBaiViet
					ORDER BY LuotBinhLuan DESC";
					
			$service = new dataservice();
			return $service->ExecuteQuery($sql);
		}
	}
?>